#!/bin/bash
export HOMEDIR=$(dirname $(dirname $(readlink -f "${BASH_SOURCE[0]}")))
source $HOMEDIR/scripts/init-env.sh

schema_list=($(jq -r '.object.schemas[]' $HOMEDIR/config/mtk-config.json))
printf -v schema_string1 "'%s'," "${schema_list[@]}"
schema_string1=${schema_string1%,}

cd $HOMEDIR/workspace/mtk

echo 授权public权限
#--2.连接MogDB,查询以下SQL，生成PUBLIC权限的语句，并在MogDB执行

$HOMEDIR/workspace/mtk/mtk -c $HOMEDIR/config/mtk-config.json usql --target -C "
DO LANGUAGE 'plpgsql'
\$BODY\$
begin
for rec in (
 select 'grant all on schema public to '||usename||';'||'grant usage on schema '||usename||' to public;' cmd
  from pg_user where upper(usename) in
($schema_string1)) loop
execute immediate rec.cmd;
end loop;
end;
\$BODY\$
"

echo 查询ORACLE权限
#--3.连接ORACLE,查询以下SQL，生成授权语句 ,并在MogDB执行
$HOMEDIR/workspace/mtk/mtk -c $HOMEDIR/config/mtk-config.json usql --source -C "
with OBJECT_GRANT as
 (SELECT grantee,
         x.owner,
         table_name,
         a.OBJECT_TYPE,
         CASE
           WHEN A.OBJECT_TYPE = 'TABLE' AND COUNT(1) = 11 THEN
            'ALL'
           WHEN A.OBJECT_TYPE IN ('PACKAGE', 'TYPE', 'SEQUENCE') AND
                COUNT(1) = 2 THEN
            'ALL'
           WHEN A.OBJECT_TYPE IN ('VIEW') AND COUNT(1) = 10 THEN
            'ALL'
           ELSE
            LISTAGG(privilege, ',') WITHIN GROUP(ORDER BY privilege)
         END privilege
    FROM dba_tab_privs x,
         (select distinct owner, object_type, object_name
            from dba_objects a
           where a.OBJECT_TYPE not in ('PACKAGE BODY', 'TYPE BODY')) a
   WHERE x.owner <> grantee
     and x.owner = a.OWNER(+)
     AND x.owner<>'SYS'
     and x.table_name = a.OBJECT_NAME(+)
     AND grantee IN 
($schema_string1)
     AND TABLE_NAME NOT LIKE 'BIN$%'
   group by x.owner, table_name, a.OBJECT_TYPE, grantee)
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
       ' to ' || grantee || ';' sqltext
  from OBJECT_GRANT
  union all
select 'grant ' || p.privilege || ' to ' || grantee || ';'
  from dba_sys_privs p
 where grantee IN 
 ($schema_string1)
and p.privilege like '% ANY %';
" -o grant_cmd.sql

sed -i '1,3d' grant_cmd.sql
sed -i '$d' grant_cmd.sql
sed -i '$d' grant_cmd.sql

if [ -s "grant_cmd.sql" ]
then
    echo 在MogDB执行授权
#    $HOMEDIR/workspace/mtk/mtk -c $HOMEDIR/config/mtk-config.json usql --target -C "`cat grant_cmd.sql`" 
# MTK 2.9.9 开始支持执行SQL文件
    $HOMEDIR/workspace/mtk/mtk -c $HOMEDIR/config/mtk-config.json usql --target -f $HOMEDIR/workspace/mtk/grant_cmd.sql 
else
    echo "源端无额外的grant权限,跳过权限同步"
fi

echo 回收管理员权限
#--1.连接MogDB,查询以下SQL，生成收回管理员权限的语句，并在MogDB执行
$HOMEDIR/workspace/mtk/mtk -c $HOMEDIR/config/mtk-config.json usql --target -C "
DO LANGUAGE 'plpgsql'
\$BODY\$
begin
for rec in (
select 'alter user '||usename||' nosysadmin nomonadmin;' cmd from pg_user where upper(usename) in
($schema_string1)) loop
execute immediate rec.cmd;
end loop;
end;
\$BODY\$
"
